package com.apes.fn.fico.settleExpense;

import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.frame.model.Dept;
import org.springframework.data.jpa.repository.Query;

import java.util.Date;

public interface SettleExpenseRepository extends CustomRepository<SettleExpense, String> {
    /**
     * fetch data by company id
     *
     * @param companyId company id
     * @param startDate date
     * @param endDate   date
     * @return List<SettleExpense>
     */
    @Query(value = "select nvl(sum(case\n" +
            "                   when t.TYPE = 'out' then\n" +
            "                       t.amount\n" +
            "                   when t.TYPE = 'in' then t.AMOUNT * -1\n" +
            "                   else 0\n" +
            "    end\n" +
            "               ), 0) SUM_AMOUNT from fn_settle_expense t where ((t.TYPE = 'out' AND t.EXPENSE_TYPE != 'platformExpense') or (t.TYPE = 'in' AND t.EXPENSE_TYPE = 'platformExpense')) AND t.PAYMENT_ID= ?1 AND  t.CREATE_DATE>=?2 and t.CREATE_DATE< ?3",nativeQuery = true)
    double findAmountByTypeAndTenant(String companyId, Date startDate, Date endDate);

    SettleExpense findBySourceAndOriginAndDeptAndExpenseTypeAndType(String source, String origin, Dept dept, String expenseType, String type);
}
